﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;

using System.Data.SqlClient;
using System.Text.RegularExpressions;

using Excel = Microsoft.Office.Interop.Excel;

namespace AbacusCustom
{
    public partial class frmImportcsv : Form
    {
        public frmImportcsv()
        {
            InitializeComponent();
        }

        private void insert_columns(String databasename, String sqlquery)
        {
            Module db_conf = new Module();

            SqlConnection conn = new SqlConnection();
            conn.ConnectionString =
            "Data Source=" + db_conf.db_host + ";" +
            "Database=" + databasename + ";" +
            "User ID=" + db_conf.db_user + ";" +
            "Password=" + db_conf.db_password + ";";
            conn.Open();

            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = sqlquery;
            cmd.ExecuteReader();

            conn.Close();
        }

        private void btnColumns_Click(object sender, EventArgs e)
        {
            if(openFileDialog1.ShowDialog()==DialogResult.OK)
            {
                String filename = openFileDialog1.FileName;

                String sqlquery = "TRUNCATE TABLE PS_COLUMN; ";
                String[] param=new String[100];

                Excel.Application xlApp = new Excel.Application();
                Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(filename);
                Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
                Excel.Range xlRange = xlWorksheet.UsedRange;

                int rowCount = xlRange.Rows.Count;
                int colCount = xlRange.Columns.Count;

                for (int i = 1; i <= rowCount; i++)
                {
                    sqlquery += "INSERT INTO PS_COLUMN(TABLE_TYPE_ID,TABLE_ID,IS_PRIMARY,NAME,LONG_NAME,DATA_TYPE,LENGTH,DESCRIPTION) VALUES('" + Regex.Replace(xlRange.Cells[i, 1].Value2.ToString(), @"[^0-9a-zA-Z]+", " ") + "','" + Regex.Replace(xlRange.Cells[i, 2].Value2.ToString(), @"[^0-9a-zA-Z]+", " ") + "','" + Regex.Replace(xlRange.Cells[i, 3].Value2.ToString(), @"[^0-9a-zA-Z]+", " ") + "','" + Regex.Replace(xlRange.Cells[i, 4].Value2.ToString(), @"[^0-9a-zA-Z]+", " ") + "','" + Regex.Replace(xlRange.Cells[i, 5].Value2.ToString(), @"[^0-9a-zA-Z]+", " ") + "','" + Regex.Replace(xlRange.Cells[i, 6].Value2.ToString(), @"[^0-9a-zA-Z]+", " ") + "','" + Regex.Replace(xlRange.Cells[i, 7].Value2.ToString(), @"[^0-9a-zA-Z]+", " ") + "','"+ Regex.Replace(xlRange.Cells[i, 8].Value2.ToString(), @"[^0-9a-zA-Z]+", " ")+"');";
                }

                GC.Collect();
                GC.WaitForPendingFinalizers();

                xlWorkbook.Close();
                xlApp.Quit();
              
                insert_columns("PSCUSTOM", sqlquery);

                MessageBox.Show("New Columns inserted","Information");
            }
        }

        private void btnTableTypes_Click(object sender, EventArgs e)
        {
            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                String filename = openFileDialog1.FileName;

                String sqlquery = "TRUNCATE TABLE PS_TABLE_TYPE; ";
                String[] param = new String[100];

                Excel.Application xlApp = new Excel.Application();
                Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(filename);
                Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
                Excel.Range xlRange = xlWorksheet.UsedRange;

                int rowCount = xlRange.Rows.Count;
                int colCount = xlRange.Columns.Count;

                for (int i = 1; i <= rowCount; i++)
                {
                    sqlquery += "INSERT INTO PS_TABLE_TYPE(NAME,DESCRIPTION) VALUES('" + Regex.Replace(xlRange.Cells[i, 1].Value2.ToString(), @"[^0-9a-zA-Z]+", " ") + "','" + Regex.Replace(xlRange.Cells[i, 2].Value2.ToString(), @"[^0-9a-zA-Z]+", " ") + "'); ";
                }

                GC.Collect();
                GC.WaitForPendingFinalizers();

                xlWorkbook.Close();
                xlApp.Quit();

                insert_columns("PSCUSTOM", sqlquery);

                MessageBox.Show("New Table Types inserted", "Information");
            }
        }

        private void btnTables_Click(object sender, EventArgs e)
        {
            if (openFileDialog1.ShowDialog() == DialogResult.OK)
            {
                String filename = openFileDialog1.FileName;
                String sqlquery = "TRUNCATE TABLE PS_TABLE; ";
                String[] param = new String[100];

                Excel.Application xlApp = new Excel.Application();
                Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(filename);
                Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[1];
                Excel.Range xlRange = xlWorksheet.UsedRange;

                int rowCount = xlRange.Rows.Count;
                int colCount = xlRange.Columns.Count;

                for (int i = 1; i <= rowCount; i++)
                {
                    sqlquery += "INSERT INTO PS_TABLE(TABLE_TYPE_ID,NAME,DESCRIPTION) VALUES('" + xlRange.Cells[i, 1].Value2.ToString() + "','" + xlRange.Cells[i, 2].Value2.ToString() + "','" + xlRange.Cells[i, 3].Value2.ToString() + "'); ";
                }

                GC.Collect();
                GC.WaitForPendingFinalizers();

                xlWorkbook.Close();
                xlApp.Quit();

                insert_columns("PSCUSTOM", sqlquery);

                MessageBox.Show("New Tables inserted", "Information");
                //file.Close();
            }
        }

        private void frmImportcsv_Load(object sender, EventArgs e)
        {
            


        }
    }
}
